Querying A PostgreSQL Table Using Psycopg2

Overview:

• PostgreSQL is a powerful open source database management system in terms of number of features supported from the SQL standards and the size of the user base.
• PostgreSQL is a fully ACID compliant database management system which adheres to the SQL: 2008 standard.
• Psycopg2 is the database adapter developed for interfacing with the PostgreSQL from any Python Program.
• This article explains the steps involved in querying a Psycopg2 from a Python program and retrieving a set of rows matching a specified where clause.

Installing psycopg2:

psycopg2 can be installed using pip as given below:

Querying from PostgreSQL table using Psycopg:

• The factory function connect() of module Psycopg2 is used for creating a connection object.
• The connect() function accepts parameters corresponding to the PostgreSQL database name and the authentication details of the PostgreSQL User.
• A cursor object is created using the obtained connection object.
• Using a cursor object any SQL Statements supported by the PostgreSQL can be executed.
• Cursors in Psycopg are not thread safe. The thread safety while using the same cursor object across multiple Python threads need to be checked with the Psycopg documentation.
• With a call to the execute () method of the cursor, a SQL statement supported by the PostgreSQL can be executed.
• Upon calling execute() method, one among the data retrieval methods like fetchone(), fetchmany() and fetchall() is called to get the actual row data for processing inside the Python program.

Example:

Output: